if exists(select name from sysobjects where name = 'sp_KhuyenMaiGetAllByLoaiKhuyenMaiId')
	drop proc sp_KhuyenMaiGetAllByLoaiKhuyenMaiId
go
/*-- =============================================
	Created by: HP
	Created on: 2014-03-09 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_KhuyenMaiGetAllByLoaiKhuyenMaiId.sql
*/-- =============================================

CREATE PROCEDURE sp_KhuyenMaiGetAllByLoaiKhuyenMaiId
@LoaiKhuyenMaiId int,
@ThanhPho tinyint,
@Price int =0,
@q Nvarchar(512)='',

    @PageIndex INT = 1,
	@PageSize INT = 10,

	@RecordCount INT=0 OUTPUT
AS
BEGIN
	SELECT ROW_NUMBER() over(
		order by  (100-CAST(GiaKM AS float)/CAST(GiaNiemYet AS float)*100) desc
	) as RowNumber,a.[KhuyenMaiId],
		a.[TieuDe],
		a.[GiaNiemYet],
		a.[GiaKM],
		a.[ThoiGian],
		a.[Link],
		a.[ImageSmall],
		a.[SiteNguon],
		a.[Provider],
		a.[Summary],
		a.[ThuTu],
		a.[TrangThai],
		a.[NguoiTao],
		a.[NgayTao],
		a.[NguoiSua],
		a.[NgaySua],
		a.[WebId]
		 into #Results
		
	FROM
        KhuyenMai a inner join LoaiKhuyenMaiAndKhuyenMai b on a.KhuyenMaiId=b.KhuyenMaiId where a.GiaKM<= case when @Price=0 then 9000000000
																												when @Price>0 then @Price
																											end																											
		and b.LoaiKhuyenMaiId=@LoaiKhuyenMaiId and a.thanhpho=@thanhpho and a.TieuDe like '%'+@q+'%'
        set @RecordCount=(select COUNT(KhuyenMaiId) from #Results)
	select * from #Results  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
	drop table #Results
END
GO




